USE [ventas]
GO

/****** Object:  View [saventa].[vw_modelos_en_lot]    Script Date: 08/16/2012 15:53:39 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[saventa].[vw_modelos_en_lot]'))
DROP VIEW [saventa].[vw_modelos_en_lot]
GO

USE [ventas]
GO

/****** Object:  View [saventa].[vw_modelos_en_lot]    Script Date: 08/16/2012 15:53:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE VIEW [saventa].[vw_modelos_en_lot]
AS
SELECT     mel.lot_id AS Codigo_lote, mod.descripcion AS Modelo, mod.area_const, mod.plantas AS Pisos, 30 AS Cuota_entrada, 10 AS Tasa, 
                      ROUND(lot.lot_pventa + mel.lot_pventa, 2) AS Precio_lista
FROM         saventa.tb_ven_mod_en_lot AS mel INNER JOIN
                      saventa.tb_ven_lot AS lot ON mel.lot_id = lot.lot_id INNER JOIN
                      saventa.tb_ven_proy_mod AS mod ON mel.mod_id = mod.mod_id_vtas AND mod.mod_ctg = 'V' AND mod.pro_sta = 'A'
WHERE lot.lot_pro NOT IN (select urb_id from ventas.saventa.tb_ven_urb_config where
parametro_id = 'CONVIVENCIAPROYECTO' and parametro_valor = 'S')

UNION

select
--IVPL.IdProducto AS CodigoMedusa,
IVPL.lot_id AS Codigo_lote,
IVPNI.Producto AS Modelo,
IVPNI.Area AS area_const,
IVPNI.CantidadPlantas as Pisos,
30 as Cuota_entrada,
10 as Tasa,
ROUND(IVPI.PrecioLista+IVPIPNI.PrecioLista,2) AS Precio_lista
from  [premedusa\sql2008].Data.InventarioVentas.ProductoLote IVPL
INNER JOIN  [premedusa\sql2008].Data.InventarioVentas.ProductoInventariable IVPI
	ON IVPL.IdProducto=IVPI.IdProductoInventariable
	and IVPL.pro_id IS NOT NULL
INNER JOIN  [premedusa\sql2008].Data.InventarioVentas.ProductoInventariableProductoNoInventariable IVPIPNI
	ON IVPIPNI.IdProductoInventariable = IVPL.IdProducto
INNER JOIN  [premedusa\sql2008].Data.InventarioVentas.ProductoNoInventariable IVPNI
 on IVPNI.IdProductoNoInventariable = IVPIPNI.IdProductoNoInventariable
where IVPL.pro_id IN (select urb_id from ventas.saventa.tb_ven_urb_config where
parametro_id = 'CONVIVENCIAPROYECTO' and parametro_valor = 'S') 






GO


